package za.co.smartmi.util;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang.StringUtils;

import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;


public class JdbcDaoUtil extends JdbcDaoSupport  {

	public List getRows(String sql, Object[] args, RowMapper rowMapper) {
		return this.getJdbcTemplate().query(eliminateNulls(sql, args), eliminateNulls(args), rowMapper);
	}
	
	public List getRows(String sql, List<Object> args, RowMapper rowMapper) {
		return getRows(sql, (Object[]) args.toArray(new Object[args.size()]), rowMapper);
	}
	
	public Object queryForObject(String sql, Object[] args, RowMapper rowMapper) {
		return this.getJdbcTemplate().queryForObject(eliminateNulls(sql, args), eliminateNulls(args), rowMapper);
	}

	public Long getIdentity() {
		return queryForLong("select @@identity", null);
	}
	
	public Timestamp getCurrentDbTime() {
		return queryForTimestamp("select getDate()", null);
	}

	public Timestamp getCurrentDbUtcTime() {
		return queryForTimestamp("select getUTCDate()", null);
	}

	public void update(String sql, Object[] args) {
		this.getJdbcTemplate().update(this.eliminateNulls(sql,args), this.eliminateNulls(args));
	}
	
	public void update(String sql, List args) {
		this.update(sql, args.toArray());
	}

	public int queryForInt(String sql, Object[] args) {
		return this.getJdbcTemplate().queryForInt(this.eliminateNulls(sql,args), this.eliminateNulls(args));
	}

	public Integer queryForInteger(String sql, Object[] args) {
		return (Integer)this.getJdbcTemplate().queryForObject(this.eliminateNulls(sql,args), this.eliminateNulls(args), Integer.class);
	}
	
	public Long queryForLong(String sql, Object[] args) {
		try {
        return (Long)this.getJdbcTemplate().queryForObject(this.eliminateNulls(sql,args), this.eliminateNulls(args), Long.class);
		} catch (IncorrectResultSizeDataAccessException ex) {
			return null;
    }
    }
	
	public Float queryForFloat(String sql, Object[] args) {
		return (Float)this.getJdbcTemplate().queryForObject(this.eliminateNulls(sql,args), this.eliminateNulls(args), Float.class);
	}
	
	public Short queryForShort(String sql, Object[] args) {
        return (Short)this.getJdbcTemplate().queryForObject(this.eliminateNulls(sql,args), this.eliminateNulls(args), Short.class);
    }
	
	public Float queryForFloat(String sql) {
		Float theFloat = null;
		try {
			theFloat = (Float)this.getJdbcTemplate().queryForObject(sql, Float.class);
		} catch (IncorrectResultSizeDataAccessException ex) {
			
	}
		return theFloat;
	}
	
	public String queryForString(String sql, Object[] args) {
		String theString = null;

		try {
			theString = (String)this.getJdbcTemplate().queryForObject(this.eliminateNulls(sql,args), this.eliminateNulls(args), String.class);
		} catch (IncorrectResultSizeDataAccessException ex) {

		}
		return (null != theString? StringUtils.trim(theString) : theString);
	}

	public Timestamp queryForTimestamp(String sql, Object[] args) {
		try {
		return (Timestamp)this.getJdbcTemplate().queryForObject(this.eliminateNulls(sql,args), this.eliminateNulls(args), Timestamp.class);
		} catch (IncorrectResultSizeDataAccessException ex) {
			return null;
	}
	}
	
//	 return a Float 
    public Float getFloat(ResultSet rs, String column) {
    	try {
	    	float value = rs.getFloat(column); 
	    	if ( rs.wasNull()) {
	    		return null;
	    	}
	    	else {
	    		return new Float(value);
	    	}
    	}
    	catch (Exception ex) {
    		return null;
    	}
    }
    // return a Integer
    public Integer getInteger(ResultSet rs, String column) {
    	try {
	    	int value = rs.getInt(column); 
	    	if ( rs.wasNull()) {
	    		return null;
	    	}
	    	else {
	    		return new Integer(value);
	    	}
    	}
    	catch (Exception ex) {
    		return null;
    	}
    }
    //  return a Long
    public Long getLong(ResultSet rs, String column) {
    	try {
	    	int value = rs.getInt(column); 
	    	if ( rs.wasNull()) {
	    		return null;
	    	}
	    	else {
	    		return new Long(value);
	    	}
    	}
    	catch (Exception ex) {
    		return null;
    	}
    }
    
//	 return a RowMapper for Integer
    protected RowMapper<Long> getLongRowMapper() {
    	 return new RowMapper<Long>() {
             public Long mapRow(ResultSet rs, int rowNum) throws SQLException {
            	 return new Long(rs.getLong(1));
             }
         };
	}
    
    protected final RowMapper<String> getStringRowMapper() {
        return new RowMapper<String>() {
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                return DatabaseHelper.getString(rs, 1);
            }
        };
    }
    
    protected final RowMapper<Integer> getIntegerRowMapper() {
        return new RowMapper<Integer>() {
            public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
                return DatabaseHelper.getInteger(rs, 1);
            }
        };
    }
    
    protected final RowMapper<Boolean> getBooleanRowMapper() {
    	return new RowMapper<Boolean>() {
    		public Boolean mapRow(ResultSet rs, int rowNum) throws SQLException {
    			return toBoolean(rs.getString(1));
    		}
    	};
    }

	/**
	 * Replace the null parameter with the null keyword. The offset
	 *  needs to be there because as more arguments are binded  with
	 *  the null keyword, the index of the question mark to replace
	 *  also changes. Best to think of it like this ...
	 *  If we have seven question marks, and arguments three, five and
	 *  seven are null, then when (h = 3) and (offset = 0), the third
	 *  argument is replaced with the null keyword. Then, the fifth
	 *  argument needs to be made null too, but it will not be the
	 *  fifth question mark, it will be the fourth. That is (h = 5)
	 *  and (offset = 1). Then the seventh argument needs to be replaced.
	 *  But that wont be the seventh question mark, it will be the (h = 7)
	 *  and (offset = 2) fifth question mark. In a nutshell, the replacement
	 *  of the question mark with the null keyword reduces the number of
	 *  question marks we have.
	 *
	 * @param sql the squirrel to eliminate null arguments from.
	 * @return the newly formed sql statement.
	 */

	public String eliminateNulls(String sql, Object[] args) {
		final String PARAMETER = "?";
		final String NULL = "null";

		// If we have arguments then ...
		if (args != null) {
			int offset = 0;
			// Trawl through the arguments ..
			for (int h = 0; h < args.length; h++) {
				// If the argument is null ...
				if (null == args[h]) {
					sql = StringUtils.replace(sql, PARAMETER, NULL, h - offset);
					offset++;
				}
			}
		}

		return sql;
	}
	/**
	 * Eliminates null arguments from the SQL string and binds
	 * null parameters (question marks) with the null keyword.
	 *
	 * @param args the arguments.
	 * @return the newly formed sql statement.
	 */

	public Object[] eliminateNulls(Object[] args) {
		List l = new ArrayList();

		if (null != args) {
			for (int i=0; i < args.length; i++) {
				if (null != args[i]) {
					l.add(args[i]);
				}
			}
		}
		return l.toArray();
	}
        
	/**
	 * Gets the boolean equivaluent of a string.
	 * Returns true for the string "1" or any string that starts with y Y t or T
	 *
	 *
	 * For example:
	 * if the lowercase string is t, true, y, yes or 1, then return true.
	 *
	 * @param val
	 * @return boolean
	 */
	public static boolean toBoolean(String val) {
		if (val == null) {
			return false;
		}

		String lcVal = val.toLowerCase();

		if (lcVal.startsWith("t") || lcVal.startsWith("y") || "1".equals(lcVal)) {
			return true;
		}

		return false;
	}        
}
